﻿using System;
using System.Data;

using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Web;
using Framework;

namespace Enterprise
{
    public class HonorService : BaseService
    {
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="name"></param>
        /// <param name="nameEn"></param>
        /// <param name="description"></param>
        /// <param name="descriptionEn"></param>
        /// <param name="smallImge"></param>
        /// <param name="normalImage"></param>
        /// <param name="bigImage"></param>
        /// <param name="sort"></param>
        /// <returns></returns>
        public int Add(string name, string nameEn, string description, string descriptionEn, string smallImge, string normalImage, string bigImage, int sort)
        {
            Check.Argument.IsNotOutOfLength(name, 50, "图片名称");
            Check.Argument.IsNotOutOfLength(nameEn, 50, "图片名称(En)");
            var sbSql = new StringBuilder();
            sbSql.Append("insert into tbHonor(");
            sbSql.Append("CoNormalImage,CoSmallImage,CoBigImage,CoName,CoDescription,CoNameEn,CoDescriptionEn,CoSort");
            sbSql.Append(") values(");
            sbSql.Append("@CoNormalImage,@CoSmallImage,@CoBigImage,@CoName,@CoDescription,@CoNameEn,@CoDescriptionEn,@CoSort");
            sbSql.Append(")");
            var parameters = new[]
                                {
                                    new SqlParameter("@CoNormalImage", SqlDbType.VarChar,255),
                                    new SqlParameter("@CoSmallImage", SqlDbType.VarChar,255),
                                    new SqlParameter("@CoBigImage", SqlDbType.VarChar,255),
                                    new SqlParameter("@CoName", SqlDbType.VarChar,255),
                                    new SqlParameter("@CoDescription", SqlDbType.VarChar),
                                    new SqlParameter("@CoNameEn", SqlDbType.VarChar,255),
                                    new SqlParameter("@CoDescriptionEn", SqlDbType.VarChar),
                                    new SqlParameter("@CoSort", SqlDbType.Int)

                                };
            parameters[0].Value = normalImage;
            parameters[1].Value = smallImge;
            parameters[2].Value = bigImage;
            parameters[3].Value = name;
            parameters[4].Value = description;
            parameters[5].Value = nameEn;
            parameters[6].Value = descriptionEn;
            parameters[7].Value = sort;
            var rows = DbHelperSql.Add(sbSql.ToString(), parameters).SafeToInt();
            if (rows > 0)
            {
                return rows;
            }
            throw new Exception("添加失败！");
        }
        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="nameEn"></param>
        /// <param name="description"></param>
        /// <param name="descriptionEn"></param>
        /// <param name="smallImge"></param>
        /// <param name="normalImage"></param>
        /// <param name="bigImage"></param>
        /// <param name="sort"></param>
        /// <returns></returns>
        public int Eidt(int id, string name, string nameEn, string description, string descriptionEn, string smallImge, string normalImage, string bigImage, int sort)
        {
            Check.Argument.IsNotOutOfLength(name, 50, "图片名称");
            Check.Argument.IsNotOutOfLength(nameEn, 50, "图片名称(En)");
            var sbSql = new StringBuilder();
            sbSql.Append("update tbHonor set ");
            sbSql.Append(" CoName=@CoName");
            sbSql.Append(",CoNameEn=@CoNameEn");
            sbSql.Append(",CoDescription=@CoDescription");
            sbSql.Append(",CoDescriptionEn=@CoDescriptionEn");
            sbSql.Append(",CoSort=@CoSort");
            sbSql.Append(",CoNormalImage=@CoNormalImage");
            sbSql.Append(",CoSmallImage=@CoSmallImage");
            sbSql.Append(",CoBigImage=@CoBigImage");
            sbSql.Append(" where CoHonorId=@id");
            var parameters = new[]
                {
                    new SqlParameter("@CoName", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoNameEn", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoDescription", SqlDbType.VarChar),
                    new SqlParameter("@CoDescriptionEn", SqlDbType.VarChar),
                    new SqlParameter("@CoSort", SqlDbType.Int),
                    new SqlParameter("@CoNormalImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoSmallImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoBigImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@id", SqlDbType.Int)
                };

            parameters[0].Value = name;
            parameters[1].Value = nameEn;
            parameters[2].Value = description;
            parameters[3].Value = descriptionEn;
            parameters[4].Value = sort;
            parameters[5].Value = normalImage;
            parameters[6].Value = smallImge;
            parameters[7].Value = bigImage;
            parameters[8].Value = id;
            var rows = DbHelperSql.ExecuteSql(sbSql.ToString(), parameters).SafeToInt();
            if (rows > 0)
            {
                return rows;
            }
            throw new Exception("修改失败！");
        }


        public void Delete(int id)
        {
            var path = HttpContext.Current.Server.MapPath("~/Upload/Honor/");
            var ds = DbHelperSql.Query("select * from tbHonor where CoHonorId={0}".FormatWith(id));
            foreach (DataRow item in ds.Tables["ds"].Rows)
            {
                FileHelper.DeleteSub(Path.Combine(path, item["CoNormalImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoSmallImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoBigImage"].SafeToString()));
            }
            int rows = DbHelperSql.ExecuteSql("delete  from tbHonor where CoHonorId={0}".FormatWith(id));
            if (!(rows > 0))
                throw new Exception("记录不存在！");
        }

        public int DeletePicture(int id)
        {
            var path = HttpContext.Current.Server.MapPath("~/Upload/Honor/");
            var ds = DbHelperSql.Query("select * from tbHonor where CoHonorId={0}".FormatWith(id));
            foreach (DataRow item in ds.Tables["ds"].Rows)
            {
                FileHelper.DeleteSub(Path.Combine(path, item["CoNormalImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoSmallImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoBigImage"].SafeToString()));
            }
            var sbSql = new StringBuilder();
            sbSql.Append("update tbHonor set CoNormalImage=@CoNormalImage,CoSmallImage=@CoSmallImage,CoBigImage=@CoBigImage where CoHonorId=@id");
            var paramTwo = new[]
                {
                    new SqlParameter("@CoNormalImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoSmallImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoBigImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@id", SqlDbType.Int)
                };
            paramTwo[0].Value = "";
            paramTwo[1].Value = "";
            paramTwo[2].Value = "";
            paramTwo[3].Value = id;
            int rows = DbHelperSql.ExecuteSql(sbSql.ToString(), paramTwo);
            return rows;

        }
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public Table GetTable(string name, int pageValue, int rowsValue)
        {
            var strWhere = new StringBuilder();
            strWhere.Append(" where (CoName like '%'+@CoName+'%' or  CoNameEn like '%'+@CoName+'%')");
            var parameter = new[]
                           {
                             new SqlParameter("@CoName",SqlDbType.VarChar, 255)
                            };
            parameter[0].Value = name.SqlLike();
            var sbSql = new StringBuilder();
            sbSql.Append("select count(*) from TbHonor ");
            sbSql.Append(strWhere);
            int total = DbHelperSql.GetSingle(sbSql.ToString(), parameter).SafeToInt();
            sbSql.Clear();
            sbSql.AppendFormat("select top {0} * from TbHonor {1} ", rowsValue, strWhere);
            if (pageValue > 1)
                sbSql.AppendFormat(" and CoHonorId not in (select top {0} CoHonorId from TbHonor {1} order by CoHonorId) ", rowsValue * (pageValue - 1), strWhere);
            sbSql.Append("  order by CoHonorId ");
            var ds = DbHelperSql.Query(sbSql.ToString(), parameter).Tables["ds"];
            var table = new Table
            {
                Rows = ds.Rows,
                Total = total
            };
            return table;
        }

        /// <summary>
        /// 获取表格
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataTable()
        {
            return DbHelperSql.Query("select * from TbHonor  order by cosort desc").Tables["ds"];
        }
        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {
            return DbHelperSql.Query("select *  from TbHonor  where CoHonorId={0}".FormatWith(id)).Tables["ds"].Rows[0];
        }
    }
}

